#!/usr/bin/env perl


use DBI;
use DBD::SQLite;
#use strict;
use Getopt::Long;#Std;
our($debug, $verbose, $help, $in_file, $out_file);
our($in_file, $out_file);

require "QSQLdata.pl";
$DBD::SQLite::COLLATION{NOCASE} = sub { $_[0] cmp $_[1] };
# h  #
# e  #
# l  # Command line options processing
# p  #
sub init()
{
    GetOptions( 'd|debug' => \$debug,
                'v|verbose' => \$verbose,
                'h|help' => \$help,
                'i|in_file:s' => \$in_file,
                'o|out_file:s' => \$out_file ) or usage();
    usage() if $help;
    if ($debug) {
            print " \$debug equals to $debug \n";
            print " \$verbose equals to $verbose \n";
            print " \$help equals to $help \n";
            print " \$in_file equals to $in_file \n";
            print " \$out_file equals to $out_file \n";
    }
    $in_file = $in_file if $in_file;
    print " Input file: ",$in_file,"\n" if $debug;
    $out_file = $out_file if $out_file;
    print " Output file: ",$out_file,"\n" if $debug;
}
#
# Message about this program and how to use it
#
sub usage()
{
    print STDERR << "EOF" ;

    usage: $0 [-hvd] [-i input_file] [-o output_file]
     -h              : this (help) message
     --help          : this (help) message
     -v              : verbose output
     --verbose       : verbose output
     -d              : print debugging messages to stderr
     --debug         : print debugging messages to stderr
     -i file         : SQlite file (default qtopia_db.sqlite)
     --in_file file  : SQlite file (default qtopia_db.sqlite)
     -o file         : vcf file (default qtopia_db.vcf)
     --out_file file : vcf file (default qtopia_db.vcf)
    example: $0 -v -d -o output_file_name

EOF
    exit;
}

print "sql2vcf".$frurl;
# Original sqlite DB
$in_file = "qtopia_db.sqlite";
# Default otput vcf file
$out_file = "qtopia_db.vcf";

# Help messages or init variables
&init();

# SQLite only data
my $id;      # ID for every user

# Future (desired) table :-)
my %rubric;  # Hash of array:  ID    -> [@names, @numbers, @emails]
my @names;   # Array of names: name, secondname (X if none), surname (X if none) 
my @numbers; # Array of phone numbers
my @emails; # Array of emails

# TMP ones
my @data;

my $dbargs = {AutoCommit => 0,
    PrintError => 1};

# Open the database
my $dbh = DBI->connect("dbi:SQLite:dbname=".$in_file,"","",$dbargs);

# Set the query for ID <-> names
my $sth_nam = $dbh->prepare('SELECT recid, lastname, firstname, middlename FROM contacts') 
    or die "Couldn't prepare statement: " . $dbh->errstr;

# Set the query for ID <-> numbers
my $sth_num = $dbh->prepare('SELECT phone_number FROM contactphonenumbers where recid = ?') 
    or die "Couldn't prepare statement: " . $dbh->errstr;

# Set the query for ID <-> emails
my $sth_email = $dbh->prepare('SELECT addr FROM emailaddresses where recid = ?') 
    or die "Couldn't prepare statement: " . $dbh->errstr;

# Execute the names query
$sth_nam->execute() 
            or die "Couldn't execute statement: " . $sth_nam->errstr;

while (($id, @names) = $sth_nam->fetchrow_array()) {
    my $lastname = $names[0];
    my $firstname   = $names[1];
    my $secname  = $names[2];
    #print "\t$id: $lastname $firstname $secname\n";
    push @{$rubric{$id}}, @names;

    # Execute the numbers query
    $sth_num->execute($id) 
        or die "Couldn't execute statement: " . $sth_num->errstr;
    while (@numbers = $sth_num->fetchrow_array()) {
         #print "\t$id: $numbers[0] $numbers[1]\n";
         push @{$rubric{$id}}, @numbers;
    }

    # Execute the emails query
    $sth_email->execute($id) 
        or die "Couldn't execute statement: " . $sth_email->errstr;
    while (@emails = $sth_email->fetchrow_array()) {
         #print "\t$id: $emails[0] $emails[1]\n";
         push @{$rubric{$id}}, @emails;
    }

}

#if ($sth_nam->rows == 0) {
    #print "No names matched \$lastname'.\n\n";
#}


#$dbh->commit();
$dbh->disconnect();

#}
#}

# Opening output file
open(OUTPUT, " > ", $out_file) or die " Can't open $out_file because $!";

# Make all names with the first letter uppercase
foreach (sort keys %rubric){
$rubric{$_}[0] = ucfirst $rubric{$_}[0];
$rubric{$_}[1] = ucfirst $rubric{$_}[1];
$rubric{$_}[2] = ucfirst $rubric{$_}[2];
}

# Sort first names in %rubric;
foreach (sort {$rubric{$a}[1] cmp $rubric{$b}[1]} keys %rubric){

my $fullname=$rubric{$_}[1];
if ($rubric{$_}[0] ne "X" && $rubric{$_}[0] ne "") {
	$fullname=$fullname." ".$rubric{$_}[0];
}
if ($rubric{$_}[2] ne "X" && $rubric{$_}[2] ne "") {
	$fullname=$fullname." ".$rubric{$_}[2];
}

print "$_ $fullname \n" if $verbose;

print OUTPUT "BEGIN:VCARD\n";
print OUTPUT "VERSION:3.0\n";
print OUTPUT "N;CHARSET=UTF-8:",$rubric{$_}[0];
print OUTPUT ";",$rubric{$_}[1] if $rubric{$_}[1];
print OUTPUT ";",$rubric{$_}[2] if $rubric{$_}[2];
print OUTPUT "\n";
print OUTPUT "FN;CHARSET=UTF-8:",$fullname,"\n";
#print OUTPUT "ORG:Bubba Gump Shrimp Co.\n";
#print OUTPUT "TITLE:Shrimp Man\n";
print OUTPUT "TEL;HOME;VOICE:",$rubric{$_}[3],"\n";
print OUTPUT "TEL;WORK;VOICE:",$rubric{$_}[4],"\n" if ($rubric{$_}[4] ne '');
print OUTPUT "TEL;HOME;CELL:",$rubric{$_}[5],"\n" if ($rubric{$_}[5] ne '');
print OUTPUT "EMAIL;PREF,INTERNET:",$rubric{$_}[6],"\n" if ($rubric{$_}[6] ne '');
#print OUTPUT "ADR;WORK:;;100 Waters Edge;Baytown;LA;30314;United States of America\n";
#print OUTPUT "LABEL;WORK:100 Waters Edge\nBaytown, LA 30314\nUnited States of America\n";
#print OUTPUT "ADR;HOME:;;42 Plantation St.;Baytown;LA;30314;United States of America\n";
#print OUTPUT "LABEL;HOME:42 Plantation St.\nBaytown, LA 30314\nUnited States of America\n";
#print OUTPUT "REV:20080424T195243Z\n";
print OUTPUT "END:VCARD\n";
print OUTPUT "\n";

#print $_," corresponds to ",$rubric{$_}[0],$rubric{$_}[1],$rubric{$_}[2],$rubric{$_}[3],$rubric{$_}[4],"\n";
}
close OUTPUT;

